#导入模块
from openpyxl import load_workbook
from copy import copy
#获取workbook
wb = load_workbook('../data/卫浴销售数据.xlsx')
#获取每一个worksheet
ws1 = wb['1月']
ws2 = wb['2月']
ws3 = wb['3月']
#获取每一个worksheet数据
data = []
for row in ws1.iter_rows(min_row=2):
    # temp_row=[]
    # for cell in row:
    #     temp_row.append(cell.value) [cell.value for cell in row]等价于上面的代码
    data.append([cell.value for cell in row])

for row in ws2.iter_rows(min_row=2):
    data.append([cell.value for cell in row])

for row in ws3.iter_rows(min_row=2):
    data.append([cell.value for cell in row])

#合并这些数据

#汇总每一个人的销量
#获取所有人名
names = [col.value for col in ws1['A']]
total = []
#添加表头
header = ['销售人员','一季度瓷砖销量','一季度马桶销量']
total.append(header)
#复制单元格样式
font = copy(ws1['A1'].font)
bordert = copy(ws1['A1'].border)
fill = copy(ws1['A1'].fill)
alignment = copy(ws1['A1'].alignment)
for name in names[1:]:
    sum1 = 0
    sum2 = 0
    for row in data:
        if row[0] == name:
            # if row[1] == None:
            #     row[1] = 0
            # if row[2] == None:
            #     row[2] = 0
            row[1] = row[1] if row[1] else 0
            row[2] = row[2] if row[2] else 0
            sum1 += row[1]
            sum2 += row[2]

    total.append([name,sum1,sum2])
#创建一个新的worksheet，写入汇总后的数据
ws = wb.create_sheet('一季度汇总表')
for i,row in enumerate(total):
    if i == 0:
        for j in range(3):
            ws.cell(row=1,column=j+1).value = row[j]
            ws.cell(row=1,column=j+1).font = font
            ws.cell(row=1,column=j+1).border = bordert
            ws.cell(row=1,column=j+1).fill = fill
            ws.cell(row=1,column=j+1).alignment = alignment
    else:
        ws.append(row)

wb.save('卫浴销售数据汇总.xlsx')
wb.close()